package com.example.dao;

import com.example.entity.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ManagerMapper {

    @Select("select * from ftos.manager where m_account = #{m_account} and m_password = #{m_password}")
    Manager getManager(@Param("m_account") String m_account, @Param("m_password") String m_password);

    //订单
    @Select("select count(*) from ftos.`order` where date = #{date}")
    int getOrderCountByDate(String date);
    @Select("select count(*) from ftos.`order` where status in ('cancelled', 'refund')")
    int getCancelledAndRefundOrderNum();
    @Select("select sum(price) from ftos.`order` where date BETWEEN #{startDate} AND #{endDate} and status in ('paid','completed')")
    String getPaidOrderMoneyByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.`order` where status = 'refund'")
    int getRefundOrderCount();
    @Select("select count(*) from ftos.`order` where date BETWEEN #{startDate} AND #{endDate} and status = 'refund'")
    String getRefundOrderNumByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.`order`")
    int getOrderNum();
    @Select("select count(*) from ftos.`order` where status = 'unpaid'")
    int getUnpaidOrderNum();
    @Select("select count(*) from ftos.`order` where status = 'paid'")
    int getPaidOrderNum();
    @Select("select count(*) from ftos.`order` where status = 'completed'")
    int getCompletedOrderNum();
    @Select("select count(*) from ftos.`order` where status = 'cancelled'")
    int getCancelledOrderNum();
    @Select("select * from ftos.`order` ORDER BY id DESC")
    List<Order> getOrderList();
    //航班
    @Select("select count(*) from ftos.flight where departure_date = #{departure_date}")
    int getFlightCountByDate(String departure_date);
    @Select("select count(*) from ftos.flight_status where status = '已延误' and date = #{date}")
    int getDelayFlightNum(String date);
    @Select("select count(*) from ftos.flight_status where status = '已取消' and date = #{date}")
    int getCancelFlightNum(String date);
    @Select("select sum(economy_space + business_space + first_space) from ftos.flight_status fs join ftos.flight f on fs.f_id = f.f_id where departure_date = #{departure_date}")
    String getFlightSpaceByDate(String departure_date);
    @Select("SELECT f.departure_airport, f.departure_city, f.departure_date, f.departure_day, f.departure_time, f.arrival_city, f.arrival_airport, f.arrival_date, f.arrival_day, f.arrival_time, f.distance, f.economy_price, f.business_price, f.first_price, f.meal_money, f.meal, f.gate, f.baggage_limit, " +
            "fs.captain, fs.assistant_captain, fs.chief_attendant, fs.flight_crew_num, fs.economy_space, fs.business_space, fs.first_space, fs.status, " +
            "p.plane_id, p.p_model, p.p_airline, p.plane_age, p.WiFi, p.capacity, p.economy_seat, p.business_seat, p.first_seat, " +
            "CASE WHEN f.departure_date = CURDATE() THEN 0 ELSE 1 END AS is_today, " +  // 标记是否是今天
            "CASE WHEN f.departure_date = CURDATE() THEN 0 ELSE DATEDIFF(f.departure_date, CURDATE()) END AS date_order " +
            "FROM ftos.flight f " +
            "JOIN ftos.flight_status fs ON f.f_id = fs.f_id " +
            "JOIN ftos.plane p ON f.plane_id = p.plane_id " +
            "LEFT JOIN ftos.ticket t ON t.flightNo = f.f_id " +
            "GROUP BY f.departure_airport, f.departure_city, f.departure_date, f.departure_day, f.departure_time, f.arrival_city, f.arrival_airport, f.arrival_date, f.arrival_day, f.arrival_time, f.distance, f.economy_price, f.business_price, f.first_price, f.meal_money, f.meal, f.gate, f.baggage_limit, " +
            "fs.captain, fs.assistant_captain, fs.chief_attendant, fs.flight_crew_num, fs.economy_space, fs.business_space, fs.first_space, fs.status, " +
            "p.plane_id, p.p_model, p.p_airline, p.plane_age, p.WiFi, p.capacity, p.economy_seat, p.business_seat, p.first_seat " +
            "ORDER BY is_today ASC, " +   // 先显示今天
            "f.departure_date DESC, " +  // 非今天按日期倒序排
            "CASE WHEN is_today = 0 THEN f.departure_time END ASC, " +  // 今天按时间正序排
            "CASE WHEN is_today = 1 THEN f.departure_time END DESC")   // 非今天按时间倒序排
    List<FlightWithStatus> getFlightList();



    //报表统计
    @Select("select sum(price) from ftos.ticket where statusText not in ('已取消','待支付')")
    BigDecimal getTotalMoney();
    @Select("select sum(price) from ftos.ticket where date BETWEEN #{startDate} AND #{endDate} and statusText not in ('已取消','待支付')")
    BigDecimal getTotalMoneyByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("SELECT count(*) FROM ftos.flight f JOIN ftos.flight_status fs ON f.f_id = fs.f_id WHERE fs.status != '已取消'")
    int getTotalFlightNum();
    @Select("select count(*) from ftos.flight f join ftos.flight_status fs on f.f_id = fs.f_id where fs.status = '已延误'")
    int getTotalDelayFlightNum();
    @Select("SELECT count(*) FROM ftos.flight f JOIN ftos.flight_status fs ON f.f_id = fs.f_id WHERE fs.status != '已取消' AND f.departure_date BETWEEN #{startDate} AND #{endDate}")
    int getTotalFlightNumByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.user")
    int getTotalUserNum();
    @Select("select count(*) from ftos.user where create_date BETWEEN #{startDate} AND #{endDate}")
    int getTotalUserNumByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("SELECT city, COUNT(*) AS count FROM ftos.user WHERE city IS NOT NULL AND city <> '' GROUP BY city ORDER BY count DESC LIMIT 6")
    List<CityCount> getTop6Cities();
    @Select({
            "SELECT departure_city AS departureCity,arrival_city AS arrivalCity,COUNT(*) AS flightCount FROM ftos.flight " +
                    "WHERE departure_date BETWEEN #{startDate} AND #{endDate} " +
                    "GROUP BY departure_city, arrival_city " +
                    "ORDER BY flightCount DESC " +
                    "LIMIT 5"
    })
    @Results({
            @Result(property = "departureCity", column = "departureCity"),
            @Result(property = "arrivalCity", column = "arrivalCity"),
            @Result(property = "flightCount", column = "flightCount")
    })
    List<TopRoute> getTop5RoutesByDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.`order` where date between #{startDate} and #{endDate}")
    int getOrderCountByLongDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.ticket where date between #{startDate} and #{endDate} and seat_type = '经济舱'")
    int getEconomyOrderCountByLongDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.ticket where date between #{startDate} and #{endDate} and seat_type = '商务舱'")
    int getBusinessOrderCountByLongDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
    @Select("select count(*) from ftos.ticket where date between #{startDate} and #{endDate} and seat_type = '头等舱'")
    int getFirstOrderCountByLongDate(@Param("startDate") String startDate, @Param("endDate") String endDate);
}
